#!/bin/bash

person_id=$((0))
periodical_id=$((0))
participation_id=$((0))
type_id=$((0))
issue_id=$((0))
article_id=$((0))
section_id=$((0))
locale_id=$((0))
article_text_id=$((0))
participant_role_id=$((0))

echo "INSERT INTO ROLE_NAMESPACE(ID, TITLE) VALUES(1, 'ARTICLE_TEXT');"
echo "INSERT INTO ROLE_NAMESPACE(ID, TITLE) VALUES(2, 'ARTICLE');"
echo "INSERT INTO ROLE_NAMESPACE(ID, TITLE) VALUES(3, 'ISSUE');"
echo "INSERT INTO ROLE_NAMESPACE(ID, TITLE) VALUES(4, 'PERIODICAL');"
echo "INSERT INTO ROLE_NAMESPACE(ID, TITLE) VALUES(5, 'OBJ');"

echo "
create or replace 
PROCEDURE LOADOBJ(file_name VARCHAR2, file_mime NCHAR, article NUMBER, participation NUMBER)
IS
  vblob    BLOB;
  vbfile   BFILE   := BFILENAME ('BLOBDIR', file_name);
  VSIZE    NUMBER;
BEGIN
  dbms_lob.CREATETEMPORARY(vblob,true);
  DBMS_LOB.fileopen (vbfile);
  VSIZE := DBMS_LOB.getlength (vbfile);
  DBMS_LOB.loadfromfile (vblob, vbfile, VSIZE);
  VSIZE := DBMS_LOB.getlength (vblob);
  INSERT INTO OBJ(DATA, ARTICLE_ID, PARTICIPATION_ID, OBJ_SIZE, MIME, FILE_NAME)
  VALUES(vblob, article, participation, VSIZE, file_mime, file_name);
  DBMS_LOB.close(vbfile);
  DBMS_LOB.FREETEMPORARY(vblob);
  COMMIT;
END;
/
"

function create_participation {
	participation_id=$(($participation_id+1))
	echo "INSERT INTO PARTICIPATION(ID) VALUES(${participation_id});"
}

while read line
do
	locale_id=$(($locale_id+1))
	code=`echo $line`
	q="INSERT INTO LOCALE(ID, CODE) VALUES(${locale_id}, '${code}');"
	echo $q
done < locale.data

while read line
do
	participant_role_id=$((${participant_role_id}+1))
	title=`echo $line | awk '{print $1}'`
	namespace=`echo $line | awk '{print $2}'`
	q="INSERT INTO PARTICIPANT_ROLE(ID, TITLE, ROLE_NAMESPACE_ID) "
	q=$q"VALUES(${participant_role_id}, '${title}', ${namespace});"
	echo $q
done < role.data

while read line
do
	person_id=$(($person_id+1))
	last_name=`echo $line | awk '{print $1}'`
	first_name=`echo $line | awk '{print $2}'`
	middle_name=`echo $line | awk '{print $3}'`
	description=`shuf -n 1 person_description.data`
	q="INSERT INTO PERSON(ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, DESCRIPTION)"
	q=$q"VALUES(${person_id}, '${first_name}', '${middle_name}', '${last_name}', '${description}');"
	echo $q
done < person_names.data

while read line
do
	type_id=$(($type_id+1))
	title=`echo $line`
	echo "INSERT INTO TYPE(ID, TITLE) VALUES(${type_id}, '${title}');"
done < type.data


while read line
do
	create_participation
	periodical_id=$((${periodical_id}+1))
	opened=`date -d "$((RANDOM%5+2000))-$((RANDOM%12+1))-$((RANDOM%28+1))" '+%d.%m.%Y'`
	opened="TO_DATE('${opened}', 'DD.MM.YYYY')"
	title=`echo $line | awk '{print $1}'`
	closed="NULL"
	parent="NULL"
	type=$(((RANDOM%${type_id})+1))

	q="INSERT INTO PERIODICAL(ID, TITLE, PARTICIPATION_ID, "
	q=$q"OPENED, TYPE_ID) VALUES("
	q=$q"${periodical_id}, '${title}', ${participation_id}, "
	q=$q"${opened}, "
	q=$q"${type});"
	echo $q

	if [[ $((RANDOM%100)) -eq 9 ]]
	then
		parent=$(((RANDOM%${periodical_id})+1))
		echo "DECLARE"
  		echo "    parent_opened DATE;"
  		echo "    parent_closed DATE;"
  		echo "    child_opened DATE;"
		echo "BEGIN"
  		echo "    SELECT opened INTO parent_opened FROM periodical WHERE id=${parent};"
		echo "    SELECT closed INTO parent_closed FROM periodical WHERE id=${parent};"
  		echo "    SELECT opened INTO child_opened FROM periodical WHERE id=${periodical_id};"
  	 	echo "    IF parent_opened < child_opened AND parent_closed IS NULL"
  	    echo "    THEN"
    	echo "        execute immediate 'UPDATE periodical SET closed=:d where id=${parent}' USING child_opened;"
    	echo "        execute immediate 'UPDATE periodical SET parent=${parent} WHERE id=${periodical_id}';"
  		echo "    END IF;"
		echo "END;"
		echo "/"
	fi
done < periodical.data

while [[ $issue_id -lt 5000 ]]
do
	create_participation
	issue_id=$(($issue_id+1))
	periodical=$(((RANDOM%${periodical_id})+1))
	num=$(((RANDOM%200)+1))
	release=`date -d "$((RANDOM%10+2003))-$((RANDOM%12+1))-$((RANDOM%28+1))" '+%d.%m.%Y'`
	release="TO_DATE('${release}', 'DD.MM.YYYY')"
	q="INSERT INTO ISSUE(ID, PERIODICAL_ID, NUM, PARTICIPATION_ID, RELEASE_DATE) "
	q=$q"VALUES(${issue_id}, ${periodical}, '${num}', ${participation_id}, ${release});"
	echo $q
done

while read line
do
	section_id=$(($section_id+1))
	title=`echo $line`
	parent=NULL
	if [[ $((RANDOM%100)) -eq 1 ]]
	then
		parent=$(((RANDOM%${section_id})+1))
		if [[ $section_id -eq $parent ]]
		then
			parent=NULL
		fi
	fi
	echo "INSERT INTO SECTION(ID, TITLE, PARENT) VALUES(${section_id}, '${title}', ${parent});"
done < section.data

while [[ $article_id -lt 15000 ]]
do
	create_participation
	article_id=$(($article_id+1))
	issue=$(((RANDOM%${issue_id})+1))
	section=$(((RANDOM%${section_id})+1))
	q="INSERT INTO ARTICLE(ID, ISSUE_ID, PARTICIPATION_ID, SECTION_ID) "
	q=$q"VALUES(${article_id}, ${issue}, ${participation_id}, ${section});"
	echo $q

	create_participation
	article_text_id=$((${article_text_id}+1))
	locale=$(((RANDOM%${locale_id})+1))
	text=`fortune | sed "s#'#''#g"`
	text=`echo $text | sed "s#&#and#g"`
	q="INSERT INTO ARTICLE_TEXT(ID, TEXT, LOCALE, ARTICLE_ID, PARTICIPATION_ID, TITLE) "
	q=$q"VALUES(${article_text_id}, '${text}', ${locale}, ${article_id}, ${participation_id}, '`shuf -n 1 section.data`');"
	echo $q
done
echo "COMMIT;"

echo "
DELETE
FROM participation p
WHERE NOT EXISTS (
		SELECT NULL
		FROM PERIODICAL
		WHERE participation_id = p.id
		)
	AND NOT EXISTS (
		SELECT NULL
		FROM ISSUE
		WHERE participation_id = p.id
		)
	AND NOT EXISTS (
		SELECT NULL
		FROM ARTICLE
		WHERE participation_id = p.id
		)
	AND NOT EXISTS (
		SELECT NULL
		FROM OBJ
		WHERE participation_id = p.id
		)
	AND NOT EXISTS (
		SELECT NULL
		FROM ARTICLE_TEXT
		WHERE participation_id = p.id
		);"

echo "
DECLARE
  person_id NUMBER;
BEGIN
FOR TEMP IN (
    SELECT ID FROM PARTICIPATION
  )
  LOOP
    FOR ROLE_ID IN (
      SELECT ID FROM PARTICIPANT_ROLE
    )
    LOOP
      BEGIN
        SELECT ID  INTO person_id 
          FROM ( SELECT * FROM PERSON ORDER BY dbms_random.value )
          WHERE rownum = 1;
        
        INSERT INTO PARTICIPANT(PARTICIPATION_ID, PARTICIPANT_ROLE_ID, PERSON_ID) 
              VALUES(TEMP.ID, ROLE_ID.ID, person_id);
        COMMIT;
        EXCEPTION
          WHEN OTHERS THEN
            ROLLBACK;
      END;
    END LOOP;
  END LOOP;
END;
/"
